package com.zj.fx.service;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.text.DecimalFormat;
import java.util.*;
import java.util.stream.Collectors;


import com.zj.fx.mapper.auto.AutoOriimportRecordMapper;
import com.zj.fx.model.auto.AutoOriimportRecord;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import cn.hutool.core.util.StrUtil;
import com.zj.fx.common.base.BaseService;
import com.zj.fx.common.support.ConvertUtil;
import com.zj.fx.mapper.auto.AutoOriimportMapper;
import com.zj.fx.model.auto.AutoOriimport;
import com.zj.fx.model.auto.AutoOriimportExample;
import com.zj.fx.model.custom.Tablepar;
import com.zj.fx.util.SnowflakeIdWorker;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

/**
 * 月度数据导入数据原始表 AutoOriimportService
 * @Title: AutoOriimportService.java 
 * @Package com.zj.fx.service 
 * @author SQL_自动生成
 * @email ${email}
 * @date 2024-02-26 09:55:53  
 **/
@Service
public class AutoOriimportService implements BaseService<AutoOriimport, AutoOriimportExample> {
    @Autowired
    private AutoOriimportMapper autoOriimportMapper;

    @Autowired
    private AutoOriimportRecordMapper autoOriimportRecordMapper;
    /**
     * 分页查询
     * @param pageNum
     * @param pageSize
     * @return
     */
    public PageInfo<AutoOriimport> list(Tablepar tablepar,AutoOriimport autoOriimport) {
            AutoOriimportExample testExample = new AutoOriimportExample();
        //搜索
        if (StrUtil.isNotEmpty(tablepar.getSearchText())) {//小窗体
            testExample.createCriteria().andLikeQuery2(tablepar.getSearchText());
        } else {//大搜索
            testExample.createCriteria().andLikeQuery(autoOriimport);
        }
        //表格排序
        //if(StrUtil.isNotEmpty(tablepar.getOrderByColumn())) {
        //	testExample.setOrderByClause(StringUtils.toUnderScoreCase(tablepar.getOrderByColumn()) +" "+tablepar.getIsAsc());
        //}else{
        //	testExample.setOrderByClause("id ASC");
        //}
        PageHelper.startPage(tablepar.getPage(), tablepar.getLimit());
        List<AutoOriimport> list = autoOriimportMapper.selectByExample(testExample);
        PageInfo<AutoOriimport> pageInfo = new PageInfo<AutoOriimport>(list);
        return pageInfo;
    }
    
        @Override
        public int deleteByPrimaryKey(String ids) {
            
                List<String> lista = ConvertUtil.toListStrArray(ids);
                    AutoOriimportExample example = new AutoOriimportExample();
                example.createCriteria().andIdIn(lista);
                return autoOriimportMapper.deleteByExample(example);

            
        }


        @Override
        public AutoOriimport selectByPrimaryKey(String id) {
            
                return autoOriimportMapper.selectByPrimaryKey(id);
            
        }


        @Override
        public int updateByPrimaryKeySelective(AutoOriimport record) {
            return autoOriimportMapper.updateByPrimaryKeySelective(record);
        }

    
    /**
     * 添加
     */
    @Override
    public int insertSelective(AutoOriimport record) {
        
            //添加雪花主键id
            record.setId(SnowflakeIdWorker.getUUID());

        
        return autoOriimportMapper.insertSelective(record);
    }


    @Override
    public int updateByExampleSelective(AutoOriimport record, AutoOriimportExample example) {

        return autoOriimportMapper.updateByExampleSelective(record, example);
    }


    @Override
    public int updateByExample(AutoOriimport record, AutoOriimportExample example) {

        return autoOriimportMapper.updateByExample(record, example);
    }

    @Override
    public List<AutoOriimport> selectByExample(AutoOriimportExample example) {

        return autoOriimportMapper.selectByExample(example);
    }


    @Override
    public long countByExample(AutoOriimportExample example) {

        return autoOriimportMapper.countByExample(example);
    }


    @Override
    public int deleteByExample(AutoOriimportExample example) {

        return autoOriimportMapper.deleteByExample(example);
    }

    @Transactional
    public Map<String, Object> addOrganizationInfo(MultipartFile file) throws IOException {
        Map<String, Object> map = new HashMap<>();

        // 定义执行结果
        String returnStr="";
        int successNum = 0;// 成功条数
        int failNum = 0;// 失败条数
        int repeatNum = 0;// 重复条数
//        this.autoOriimportMapper`.delete();
        List<AutoOriimport> list =importExcel(file.getInputStream());
        List<AutoOriimport> repeatList = new ArrayList<AutoOriimport>();
        for (int i = 0; i < list.size() - 1; i++) {
            for (int j = list.size() - 1; j > i; j--) {
                if (list.get(j).getTargetId().equals(list.get(i).getTargetId()) && list.get(j).getSupplyName().equals(list.get(i).getSupplyName()) && list.get(j).getTaskmonth().equals(list.get(i).getTaskmonth())) {
                    repeatList.add(list.get(j));//把相同元素加入list(找出相同的)
                    list.remove(j);//删除重复元素
                }
            }
        }
        if(repeatList.size()>0){
            String returnText = "";
            for (int i = 0; i < repeatList.size(); i++) {
                returnText=returnText+ repeatList.get(i).getSupplyName()+"供电所年月:"+repeatList.get(i).getTaskmonth()+"，指标id:"+repeatList.get(i).getTargetId();
                if(i != repeatList.size()-1 && repeatList.size()>1){
                    returnText=returnText+"、";
                }
            }
            returnText=returnText+"存在重复数据，请重新整理数据模板";
            map.put("code", 200);
            map.put("msg", returnText);
            return map;
        }

        if(list.size()<1) {
            map.put("code", 200);
            map.put("msg", "导入的数据存在问题");
            return map;
        }

        List<AutoOriimport> list1 = list.stream().collect(Collectors.collectingAndThen(Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(u -> u.getTargetId() + ";" + u.getTaskmonth()))), ArrayList::new));
        for (AutoOriimport o : list1) {
            autoOriimportMapper.deleteByTargetIdAndTaskmonth(o.getTargetId(),o.getTaskmonth());
        }


        for (AutoOriimport entity2 : list) {

            if (autoOriimportMapper.insert(entity2) > 0) {
                successNum = successNum + 1;
            } else {
                failNum = failNum + 1;
                returnStr = returnStr + "在" + entity2.getId() + "行发生错误[数据库执行失败]<br>";

            }
        }
        AutoOriimportRecord autoOriimportRecord = new AutoOriimportRecord();
        autoOriimportRecord.setId(SnowflakeIdWorker.getUUID());
        autoOriimportRecord.setImportTime(new Date());
        if(failNum>0){
            System.err.println("导入成功["+successNum+"]条数据，导入失败["+failNum+"]条数据<br>" + returnStr);
            map.put("code", 0);
            map.put("msg", "导入成功["+successNum+"]条数据，导入失败["+failNum+"]条数据<br>" + returnStr);

            autoOriimportRecord.setTotalCount((successNum+failNum)+"");
            autoOriimportRecord.setSuccessCount(successNum+"");
            autoOriimportRecord.setFailCount(failNum+"");
            autoOriimportRecordMapper.insertSelective(autoOriimportRecord);
            return map;
        }else{
            map.put("code", 0);
            map.put("msg", "导入成功["+successNum+"]条数据");
            autoOriimportRecord.setTotalCount(successNum+"");
            autoOriimportRecord.setSuccessCount(successNum+"");
            autoOriimportRecord.setFailCount("0");
            autoOriimportRecordMapper.insertSelective(autoOriimportRecord);
            return map;
        }

    }

    public  List<AutoOriimport>  importExcel(InputStream inputStream) throws IOException {

        // 定义一个List，用来存放excel表中的值
        List<AutoOriimport> list2 = new ArrayList<AutoOriimport>();


        // 1、传入获取的工作流, workbook,是一个工作簿，使用excel能操作的这边它都可以操作，这一个操作，相当于是把Excel放到Java中使用
        Workbook wb = new HSSFWorkbook(inputStream);

        String isTwo = "";
        // 2、获取工作簿中的表,对表中的设置，通过下标去拿第一张表
        Sheet sheet = wb.getSheetAt(0);
        // 3、获取表格总的行数
        int rowNumber = sheet.getPhysicalNumberOfRows();

        // 从第二行开始读取每一行数据，我们设置的模板表头是在第一、第二行，下标从0开始。
        for (int rowNum = 1; rowNum < rowNumber; rowNum++) {
            // 每读取一行,实例化一个对象
            AutoOriimport map1 = new AutoOriimport();
            // 获取工作表中第二行的数据
            Row rowData = sheet.getRow(rowNum);
            if (rowData != null) {
                map1.setId(SnowflakeIdWorker.getUUID());

                Cell cell = rowData.getCell(0);
                cell.setCellType(CellType.STRING);
                map1.setSupplyId(rowData.getCell(0).getRichStringCellValue().getString());
                map1.setSupplyName(importExcel(rowData.getCell(1),wb));
                map1.setSubcompanyId(importExcel(rowData.getCell(2),wb));
                map1.setSubcompanyName(importExcel(rowData.getCell(3),wb));
                map1.setTaskmonth(importExcel(rowData.getCell(4),wb));
                map1.setTypeMemo(importExcel(rowData.getCell(5),wb));
                map1.setTargetId(importExcel(rowData.getCell(6),wb));
                cell = rowData.getCell(7);
                cell.setCellType(CellType.STRING);
                map1.setValue(rowData.getCell(7).getRichStringCellValue().getString());
                map1.setUnit(importExcel(rowData.getCell(8),wb));
                list2.add(map1);

            }
        }
        // 循环结束后，把从excel表读取的对象放到excelList中。
        // 关闭流
        inputStream.close();

        // 返回list结果
        return list2;
    }

    public  static String  importExcel(Cell cell, Workbook wb) throws IOException {
        String value = "";
        //后面使用它来执行计算公式
        FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) wb);
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                value = cell.getRichStringCellValue().getString();
                break;
            case  Cell.CELL_TYPE_NUMERIC:
                value =String.valueOf(cell.getNumericCellValue()).substring(0,String.valueOf(cell.getNumericCellValue()).length()-2);
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_BLANK:
                value = "";
                break;
            case Cell.CELL_TYPE_FORMULA:
//             value = String.valueOf(cell.getNumericCellValue());
                String cellFormula = cell.getCellFormula();
                CellValue evaluate = formulaEvaluator.evaluate(cell);
                value = Objects.equals("#N/A",evaluate.formatAsString())?"":evaluate.formatAsString();
                break;
            default:
                value = cell.toString();
                break;
        }
        return value;
    }




}
